/*
 * DCE400服务系统 -- 后端服务接口 -- 数据访问层 -- 工单数据访问
 * 包括：
 */
package dao

import (
	"errors"
	"fmt"
	"strconv"
	"time"

	"gitee.com/baal1990/gitee.com/baal1990/cmd/server_attence/app/entity"
	"gitee.com/baal1990/gitee.com/baal1990/cmd/server_attence/app/global"
	"gitee.com/baal1990/gitee.com/baal1990/pkg/db"
	"gitee.com/baal1990/gitee.com/baal1990/pkg/util"
	"go.uber.org/zap"
)

// ********************************************
// Func  ：查询数据库：加班列表分页查询。
// Method：startDate 格式 yyyy-mm-dd hh:MM:dd
// Usage ：
//
//		page 页码
//		pageSize 页面大小，若为0，则不分页
//		overtimeType 请假类型,
//		status 申请状态
//		appType 申请类型（加班 假期）,
//	startDate, endDate string, applicantId, deptId int64
//
// ********************************************
func QueryApplicationList(page, pageSize, detailType, status, appType int,
	startDate, endDate string, applicantIds []int64, deptIds []int64) (int64, []*entity.ApplicationInfo, error) {

	offset := (page - 1) * pageSize
	session := db.Session().Table("application") //工单
	defer session.Close()

	if pageSize > 0 {
		session.Limit(pageSize, offset)
	}
	if detailType > entity.EDetailType_Unknow && detailType <= entity.EDetailType_OfficialHoliday {
		session = session.And("application.type=?", detailType)
	}
	if status > entity.EApplicationStatus_Draft && status <= entity.EApplicationStatus_Pass {
		session = session.And("application.status=?", status)
	}

	if startDate != "" {
		dtLayout := "2006-01-02 15:04:05" //严格按照time包要求的格式
		loc := db.TZLocation()            //转换日期的时区
		_, serr := time.ParseInLocation(dtLayout, startDate, loc)
		if serr == nil {
			session = session.And("application.start_date >= ?", startDate)
		}
	}
	if endDate != "" {
		dtLayout := "2006-01-02 15:04:05" //严格按照time包要求的格式
		loc := db.TZLocation()            //转换日期的时区
		_, eerr := time.ParseInLocation(dtLayout, endDate, loc)
		if eerr == nil {
			session = session.And("application.end_date <= ?", endDate)
		}
	}

	if len(applicantIds) > 0 {
		var applicantSql string
		for _, applicantId := range applicantIds {
			if len(applicantSql) > 0 {
				applicantSql += " or "
			}
			applicantSql += fmt.Sprintf(" %s=%s ", "application.applicant_id", strconv.FormatInt(applicantId, 10))
		}
		session = session.And(applicantSql)
	}

	if len(deptIds) > 0 { //部门
		var deptSql string
		for _, deptId := range deptIds {
			if len(deptSql) > 0 {
				deptSql += " or "
			}
			deptSql += fmt.Sprintf(" %s=%s ", "application.dept_id", strconv.FormatInt(deptId, 10))
		}
		session = session.And(deptSql)
	}

	if appType == entity.EApplicationType_Overtime || appType == entity.EApplicationType_Vacation {
		session = session.And("application.application_type=?", appType)
	} else {
		//合并查询请假和加班申请
	}

	applicationList := []*entity.ApplicationInfo{}
	total, err := session.Omit("applicant_name,approver_name,dept_name,duration_day,apply_type,apply_status").
		Cols("application.id,applicant_id,application.dept_id,reason,approver_id,start_date,"+
			"end_date,duration,application.create_at,application.approve_at,application.type,application.status,note,"+
			"application.application_type,department2.dept_id,department2.dept_name").
		Join("INNER", "department2", "application.dept_id = department2.dept_id").
		Desc("application.create_at").
		FindAndCount(&applicationList)

	if err != nil {
		global.Log.Error("sql qurey failed: ", zap.Any("error", err))
		return 0, nil, err
	}

	employeeMap := GetAllEmployeeMap()
	for _, v := range applicationList {
		v.ApplyType = ApplicationDetailTypeString(v.Type)
		v.ApplyStatus = ApplicationStatusString(v.Status) //这个请假和加班是通用的
		if employeeMap != nil {
			user, has := employeeMap[v.ApproverId]
			if has {
				v.ApproverName = user.Name
			}

			user, has = employeeMap[v.ApplicantId]
			if has {
				v.ApplicantName = user.Name
			}
		}
	}
	return total, applicationList, nil
}

// ********************************************
// Func  ：查询数据库：加班列表分页查询。
// Method：startDate 格式 yyyy-mm-dd hh:MM:dd
// Usage ：
//
//		page 页码
//		pageSize 页面大小，若为0，则不分页
//		overtimeType 请假类型,
//		status 申请状态
//		appType 申请类型（加班 假期）,
//	startDate, endDate string, applicantId, deptId int64
//
// ********************************************
func QueryApplicationListForAttendance(page, pageSize, detailType, status, appType int,
	startDate, endDate string, applicantIds []int64, deptIds []int64) (int64, []*entity.Application, error) {

	offset := (page - 1) * pageSize
	session := db.Session().Table("application") //工单
	defer session.Close()

	if pageSize > 0 {
		session.Limit(pageSize, offset)
	}
	if detailType > entity.EDetailType_Unknow && detailType <= entity.EDetailType_OfficialHoliday {
		session = session.And("application.type=?", detailType)
	}
	if status > entity.EApplicationStatus_Draft && status <= entity.EApplicationStatus_Pass {
		session = session.And("application.status=?", status)
	}

	if startDate != "" && endDate != "" {
		dtLayout := "2006-01-02 15:04:05" //严格按照time包要求的格式
		loc := db.TZLocation()            //转换日期的时区
		_, serr := time.ParseInLocation(dtLayout, startDate, loc)
		_, eerr := time.ParseInLocation(dtLayout, endDate, loc)
		if serr != nil {
			return 0, nil, serr
		}
		if eerr != nil {
			return 0, nil, eerr
		}

		//var dateSql string
		dateSql := fmt.Sprintf("( %s <= '%s' and %s >= '%s')", "application.start_date", endDate, "application.end_date", startDate)
		/*dateSql += fmt.Sprintf("( %s < '%s' and %s > '%s' and %s <= '%s' )", "application.start_date", startDate, "application.end_date", startDate, "application.end_date", endDate)
		dateSql += " or "
		dateSql += fmt.Sprintf("( %s >= '%s' and %s <= '%s' and %s >= '%s' and %s <= '%s' )", "application.start_date", startDate, "application.start_date", endDate, "application.end_date", startDate, "application.end_date", endDate)
		dateSql += " or "
		dateSql += fmt.Sprintf("( %s < '%s' and %s >= '%s')", "application.start_date", startDate, "application.end_date", endDate)
		dateSql += " or "
		dateSql += fmt.Sprintf("( %s >= '%s' and %s < '%s' and %s > '%s')", "application.start_date", startDate, "application.start_date", endDate, "application.end_date", endDate)
		*/
		session = session.And(dateSql)
	}

	if len(applicantIds) > 0 {
		var applicantSql string
		for _, applicantId := range applicantIds {
			if len(applicantSql) > 0 {
				applicantSql += " or "
			}
			applicantSql += fmt.Sprintf(" %s=%s ", "application.applicant_id", strconv.FormatInt(applicantId, 10))
		}
		session = session.And(applicantSql)
	}

	if len(deptIds) > 0 { //部门
		var deptSql string
		for _, deptId := range deptIds {
			if len(deptSql) > 0 {
				deptSql += " or "
			}
			deptSql += fmt.Sprintf(" %s=%s ", "application.dept_id", strconv.FormatInt(deptId, 10))
		}
		session = session.And(deptSql)
	}

	if appType == entity.EApplicationType_Overtime || appType == entity.EApplicationType_Vacation {
		session = session.And("application.application_type=?", appType)
	} else {
		//合并查询请假和加班申请
	}

	applicationList := []*entity.Application{}
	/*total, err := session.
	Cols("application.id,applicant_id,application.dept_id,reason,approver_id,start_date,"+
		"end_date,duration,application.create_at,application.approve_at,application.type,application.status,note,"+
		"application.application_type,department2.dept_id,department2.dept_name").FindAndCount(&applicationList)*/
	total, err := session.FindAndCount(&applicationList)
	if err != nil {
		global.Log.Error("sql qurey failed: ", zap.Any("error", err))
		return 0, nil, err
	}
	return total, applicationList, nil
}

// ********************************************
// Func  ：查询数据库：指定加班的详情
// Method：GET
// Usage ：
// ********************************************
func QueryApplicationDetail(Id int64) (*entity.Application, error) {
	session := db.Session().Table("application")
	defer session.Close()

	var app entity.Application
	has, err := session.Where("id=?", Id).Get(&app)
	if err != nil || has == false {
		return nil, err
	}
	return &app, nil
}

// 提供连表查询更详细的内容
func QueryApplicationInfo(Id int64) (*entity.ApplicationInfo, error) {
	var appInfo entity.ApplicationInfo

	session := db.Session().Table("application")
	defer session.Close()

	has, err := db.Session().Table("application").Where("id=?", Id).
		Cols("application.id,applicant_id,application.dept_id,reason,approver_id,application.start_date,"+
			"application.end_date,duration,application.create_at,approve_at,application.type,application.status,note,"+
			"application.application_type,department2.dept_id,department2.dept_name").
		Join("INNER", "department2", "application.dept_id = department2.dept_id").
		Get(&appInfo)

	if err != nil || has == false {
		return nil, err
	}

	applicant, err := QueryEmployeeDetail("", appInfo.ApplicantId)
	if err == nil && applicant != nil {
		appInfo.ApplicantName = applicant.Name
	} else {
		global.Log.Info("QueryOvertimeInfo no found applicant: ", zap.Any("ApplicantId:", appInfo.ApplicantId))
	}

	approver, err := QueryEmployeeDetail("", appInfo.ApproverId)
	if err == nil && approver != nil {
		appInfo.ApproverName = approver.Name // 如果有approverid，没有名字，那么查无此人，说明这个人已经不在了。
	} else {
		global.Log.Info("QueryOvertimeInfo no found approver: ", zap.Any("ApproverId:", appInfo.ApproverId))
	}
	appInfo.DurationDay = appInfo.Duration
	appInfo.ApplyType = ApplicationDetailTypeString(appInfo.Type)
	appInfo.ApplyStatus = ApplicationStatusString(appInfo.Status)
	return &appInfo, nil
}

// ********************************************
// Func  ：添加新加班
// Method：POST
// Usage ：
// ********************************************
func InsertApplication(application entity.Application) (*entity.Application, error) {

	if application.ApplicationType != entity.EApplicationType_Overtime &&
		application.ApplicationType != entity.EApplicationType_Vacation {
		return nil, errors.New("The type of application was not specified")
	}

	application.CreateAt = time.Now()
	application.Status = entity.EOvertimeStatus_Send
	application.Id = util.GenSnowflakeInt64() //生成ID
	global.Log.Info("InsertApplication: ", zap.Any("application:", application))

	session := db.Session().Table("application")
	defer session.Close()
	err := session.Begin()
	if err != nil {
		return nil, errors.New("db session begin failed.")
	}
	_, err = session.Insert(&application) //插入
	if err != nil {
		session.Rollback()
		return nil, err
	}
	err = session.Commit()
	if err != nil {
		return nil, err
	}
	return &application, nil
}

// 加班类型信息
func ApplicationDetailTypeString(t int) string {
	if t == entity.EDetailType_Sick {
		return "病假"
	} else if t == entity.EDetailType_Personal {
		return "事假"
	} else if t == entity.EDetailType_Annual {
		return "年假"
	} else if t == entity.EDetailType_Marriage {
		return "婚假"
	} else if t == entity.EDetailType_Funeral {
		return "丧假"
	} else if t == entity.EDetailType_Workday {
		return "工作日加班"
	} else if t == entity.EDetailType_Weekend {
		return "周末加班"
	} else if t == entity.EDetailType_OfficialHoliday {
		return "法定假日加班"
	} else {
		return "其他"
	}
}

// 加班状态信息
func ApplicationStatusString(status int) string {
	if status == entity.EOvertimeStatus_Draft {
		return "编辑中"
	} else if status == entity.EOvertimeStatus_Send {
		return "已提交待审批"
	} else if status == entity.EOvertimeStatus_Delete {
		return "申请已删除"
	} else if status == entity.EOvertimeStatus_Recall {
		return "撤销"
	} else if status == entity.EOvertimeStatus_Refuse {
		return "审批拒绝"
	} else if status == entity.EOvertimeStatus_PassAndCancel {
		return "通过后撤销"
	} else if status == entity.EOvertimeStatus_Pass {
		return "审批通过"
	}
	return ""
}

// 假期类型信息
func VacationTypeString(vacationType int) string {
	if vacationType == entity.EVacationType_Sick {
		return "病假"
	} else if vacationType == entity.EVacationType_Personal {
		return "事假"
	} else if vacationType == entity.EVacationType_Annual {
		return "年假"
	} else if vacationType == entity.EVacationType_Marriage {
		return "婚假"
	} else if vacationType == entity.EVacationType_Funeral {
		return "丧假"
	} else { //if vacationType == entity.EVacationType_Marriage{
		return "其它"
	}
}

// ********************************************
// Func  ：更新加班信息
// Method：POST
// Usage ：修改： 审批人ID、ApproveAt、Status。或者特殊的cols的内容
// ********************************************
func UpdateApplication(application entity.Application, cols string) error {
	var err error
	if application.Id <= 0 {
		return errors.New("application id is none")
	}
	session := db.Session().Table("application")
	defer session.Close()
	err = session.Begin()
	if err != nil {
		return err
	}

	if cols == "" {
		cols = "approver_id,approve_at,status"
	}
	_, err = session.Where("id=?", application.Id).Cols(cols).Update(application)
	if err != nil {
		return err
	}
	err = session.Commit()
	return err
}

// ********************************************
// Func  ：批量更新加班信息
// Method：POST
// Usage ：修改： 审批人ID、ApproveAt、Status。或者特殊的cols的内容
// ********************************************
func UpdateApplicationBatch(applicationList []entity.Application, cols string) error {
	var err error
	if len(applicationList) <= 0 {
		return errors.New("application id is none")
	}
	session := db.Session().Table("application")
	defer session.Close()
	err = session.Begin()
	if err != nil {
		return err
	}

	if cols == "" {
		cols = "approver_id,approve_at,status"
	}
	for _, app := range applicationList {
		_, err = session.Where("id=?", app.Id).Cols(cols).Update(app)
		if err != nil {
			session.Rollback()
			return err
		}
	}

	err = session.Commit()
	return err
}

// ********************************************
// Func  ：删除加班数据
// Method：DELETE
// Usage ：物理删
// ********************************************
func DeleteApplication(overtimeId int64) error {
	if overtimeId <= 0 {
		return errors.New("overtime id is none")
	}

	session := db.Session().Table("application")
	defer session.Close()
	session.Begin()

	cnt, err := session.Where("id=?", overtimeId).Delete(&entity.Overtime{})
	if err != nil {
		session.Rollback()
		return err
	}
	if cnt <= 0 {
		session.Rollback()
		return errors.New("remove 0 overtime")
	}
	err = session.Commit()
	return err
}
